--    Program: 		CAPPOIMP_DOCUMENT_TYPE_B.sql
--    Description:  Import PO DOCUMENT TYPE information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_position_structure_id  number;
                    		                 		
   cursor cur_po_document_type is
      select * from CRST_PO_DOCUMENT_TYPES_ALL_B;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_po_document_type loop  
   	 l_err_msg_tmp := '';
   	 
   	 l_position_structure_id := null;
   	 if cur.DEFAULT_APPROVAL_PATH_ID is not null then
   	   begin
   	   	 select position_structure_id_n 
   	   	 into   l_position_structure_id
         from   crst_per_position_structures
         where  position_structure_id = cur.DEFAULT_APPROVAL_PATH_ID;
	   exception
	   	 when others then
	   	   l_err_msg_tmp := 'New DEFAULT_APPROVAL_PATH_ID not found';	
	   end;	   	
     end if; 	
     
     begin
       -- import data
       insert into PO_DOCUMENT_TYPES_ALL_B
       (
          DOCUMENT_TYPE_CODE              ,
          DOCUMENT_SUBTYPE                ,
          LAST_UPDATE_DATE                ,
          LAST_UPDATED_BY                 ,
          CREATED_BY                      ,
          CREATION_DATE                   ,
          ARCHIVE_EXTERNAL_REVISION_CODE  ,
          CAN_PREPARER_APPROVE_FLAG       ,
          FORWARDING_MODE_CODE            ,
          CAN_CHANGE_FORWARD_FROM_FLAG    ,
          CAN_APPROVER_MODIFY_DOC_FLAG    ,
          CAN_CHANGE_APPROVAL_PATH_FLAG   ,
          CAN_CHANGE_FORWARD_TO_FLAG      ,
          QUOTATION_CLASS_CODE            ,
          DEFAULT_APPROVAL_PATH_ID        ,
          LAST_UPDATE_LOGIN               ,
          ATTRIBUTE_CATEGORY              ,
          ATTRIBUTE1                      ,
          ATTRIBUTE2                      ,
          ATTRIBUTE3                      ,
          ATTRIBUTE4                      ,
          ATTRIBUTE5                      ,
          ATTRIBUTE6                      ,
          ATTRIBUTE7                      ,
          ATTRIBUTE8                      ,
          ATTRIBUTE9                      ,
          ATTRIBUTE10                     ,
          ATTRIBUTE11                     ,
          ATTRIBUTE12                     ,
          ATTRIBUTE13                     ,
          ATTRIBUTE14                     ,
          ATTRIBUTE15                     ,
          SECURITY_LEVEL_CODE             ,
          ACCESS_LEVEL_CODE               ,
          DISABLED_FLAG                   ,
          REQUEST_ID                      ,
          PROGRAM_APPLICATION_ID          ,
          PROGRAM_ID                      ,
          PROGRAM_UPDATE_DATE             ,
          ORG_ID                          ,
          WF_APPROVAL_ITEMTYPE            ,
          WF_APPROVAL_PROCESS             ,
          WF_CREATEDOC_ITEMTYPE           ,
          WF_CREATEDOC_PROCESS             
       )
       values
       (
              cur.DOCUMENT_TYPE_CODE              ,
              cur.DOCUMENT_SUBTYPE                ,
              cur.LAST_UPDATE_DATE                ,
              CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
              CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
              cur.CREATION_DATE                   ,
              cur.ARCHIVE_EXTERNAL_REVISION_CODE  ,
              cur.CAN_PREPARER_APPROVE_FLAG       ,
              cur.FORWARDING_MODE_CODE            ,
              cur.CAN_CHANGE_FORWARD_FROM_FLAG    ,
              cur.CAN_APPROVER_MODIFY_DOC_FLAG    ,
              cur.CAN_CHANGE_APPROVAL_PATH_FLAG   ,
              cur.CAN_CHANGE_FORWARD_TO_FLAG      ,
              cur.QUOTATION_CLASS_CODE            ,
              l_position_structure_id             ,
              cur.LAST_UPDATE_LOGIN               ,
              cur.ATTRIBUTE_CATEGORY              ,
              cur.ATTRIBUTE1                      ,
              cur.ATTRIBUTE2                      ,
              cur.ATTRIBUTE3                      ,
              cur.ATTRIBUTE4                      ,
              cur.ATTRIBUTE5                      ,
              cur.ATTRIBUTE6                      ,
              cur.ATTRIBUTE7                      ,
              cur.ATTRIBUTE8                      ,
              cur.ATTRIBUTE9                      ,
              cur.ATTRIBUTE10                     ,
              cur.ATTRIBUTE11                     ,
              cur.ATTRIBUTE12                     ,
              cur.ATTRIBUTE13                     ,
              cur.ATTRIBUTE14                     ,
              cur.ATTRIBUTE15                     ,
              cur.SECURITY_LEVEL_CODE             ,
              cur.ACCESS_LEVEL_CODE               ,
              cur.DISABLED_FLAG                   ,
              cur.REQUEST_ID                      ,
              cur.PROGRAM_APPLICATION_ID          ,
              cur.PROGRAM_ID                      ,
              cur.PROGRAM_UPDATE_DATE             ,
              CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
              cur.WF_APPROVAL_ITEMTYPE            ,
              cur.WF_APPROVAL_PROCESS             ,
              cur.WF_CREATEDOC_ITEMTYPE           ,
              cur.WF_CREATEDOC_PROCESS             
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;
     
	 if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  
   end loop;

	DBMS_OUTPUT.PUT_LINE(l_rejected_count);
	
   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_DOCUMENT_TYPE_B', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
